'Imports Microsoft.Office.Interop
'Imports Microsoft.Office.Interop.Excel
Imports System.IO
Imports System.Data.SqlClient

Imports Excel
Imports Word

Public Class FrmTekeningenlijst

    Dim strconnectionstring As String = My.Settings.Docregdata2ConnectionString
    Dim objconnection As New SqlConnection(strconnectionstring)
    Public objdataAdapterlijst As New SqlDataAdapter()
    Dim objdataset As New DataSet
    Dim wb As Excel.Workbook

    Public Sub FrmTekeningenlijst_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
       
        vullen_tekeningenlijst()

    End Sub

    Public Sub vullen_tekeningenlijst()

        objdataset.Reset()

        'Create your SqlCommand 
        Dim werknummer As String = My.Forms.FrmInlogscherm.CmbWerknummer.SelectedValue.ToString

        Dim objcmdTeklijst As New SqlCommand("Vullen_Tekeningenlijst8", objconnection)
        objcmdTeklijst.CommandType = CommandType.StoredProcedure
        objcmdTeklijst.Parameters.AddWithValue("@Werknummer", werknummer)

        'Set the SelectCommand property of our adapter  
        objdataAdapterlijst.SelectCommand = objcmdTeklijst
        objconnection.Open()
        'Fill the DataSet using the SqlDataAdapter  
        objdataAdapterlijst.Fill(objdataset, "Tekeningenlijst2")
        objconnection.Close()
        TekeningenlijstDataGridView.AutoGenerateColumns = True
        TekeningenlijstDataGridView.DataSource = objdataset.Tables("Tekeningenlijst2")

    End Sub

    Public Sub BtnPrintTekeningenlijst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnPrintTekeningenlijst.Click

        'Aanmaken excel application en worksheet en opslaan als.
        Dim ex As New Excel.Application
        'wb = ex.Workbooks.Add("C:\Documents and Settings\Hanzie\My Documents\tekeningenlijst1.xls")
        wb = ex.Workbooks.Add(System.AppDomain.CurrentDomain.BaseDirectory & "\tekeningenlijst1.xls")
        Dim sheet As Excel.Worksheet = wb.Worksheets("Blad2")

        Dim sheet1 As Excel.Worksheet = wb.Worksheets("Blad1")
        sheet1.Range("B3").Value = My.Forms.FrmInlogscherm.WerkomschrijvingTextBox.Text
        sheet1.Range("B4").Value = My.Forms.FrmInlogscherm.PlaatsTextBox.Text
        sheet1.Range("B5").Value = My.Forms.FrmInlogscherm.CmbWerknummer.SelectedValue.ToString
        sheet1.Range("E4").Value = My.Forms.FrmInlogscherm.ArchitectTextBox.Text
        sheet1.Range("E5").Value = My.Forms.FrmInlogscherm.ConstructeurTextBox.Text

        Dim datum As String = My.Computer.Clock.LocalTime.Date
        Dim tijd As String = My.Computer.Clock.LocalTime.Hour & " " & My.Computer.Clock.LocalTime.Minute
        'Dim strTekLijst As String = "C:\Documents and Settings\Hanzie\My Documents\" & "Tekeningenlijst" & " " & datum & " " & tijd & ".xls"
        Dim Directory As String = My.Forms.FrmInlogscherm.txtProjectdir.Text & "\Tekeningenlijst\"
        Dim Naam As String = "Tekeninglijst" & " " & My.Forms.FrmInlogscherm.CmbWerknummer.Text & " " & datum & " " & tijd & ".xls"

        If System.IO.Directory.Exists(Directory) Then
            wb.SaveAs(Directory & Naam)
        Else
            System.IO.Directory.CreateDirectory(Directory)
            wb.SaveAs(Directory & Naam)
        End If

        'wb.SaveAs(strTekLijst, FileFormat:=56)
        wb.Close()

        'vullen van tekeningenlijst vanuit database
        Dim objexcel As New SqlCommand
        objexcel.CommandType = CommandType.Text
        Dim strcommand As String = "insert into openrowset('microsoft.jet.oledb.4.0','excel 8.0;HDR=Yes;database=" & Directory & Naam & "','select Bedrijfsnaam, Bladnr, Naam, Datum1, Datum2, Datum3, Datum4, Datum5, Datum6, Datum7, Datum8, Datum9, Datum10, Status FROM [blad2$A1:N2]')" & "Select Bedrijfsnaam, Bladnr, Naam, Datum1, Datum2, Datum3, Datum4, Datum5, Datum6, Datum7, Datum8, Datum9, Datum10, Status FROM dbo.Tekeningenlijst"
        'Dim strcommand As String = "insert into openrowset('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;database=c:\documents and settings\Hanzie\Mijn Documenten\Tekeningenlijst2007.xlsx;','select BedrID, Bladnr, Naam, Datum1, Datum2, Datum3, Datum4, Datum5, Datum6, Datum7, Datum8, Datum9, Datum10, Status FROM [blad2$A1:N2]')" & "Select BedrID, Bladnr, Naam, Datum1, Datum2, Datum3, Datum4, Datum5, Datum6, Datum7, Datum8, Datum9, Datum10, Status FROM dbo.Tekeningenlijst"
        objconnection.Open()

        With objexcel
            .CommandText = strcommand
            .Connection = objconnection
            .ExecuteReader()
        End With

        objconnection.Close()

        'Activeren Tekeningenlijst
        'ex.Visible = True
        'wb.Activate()


    End Sub

    Public Sub BtnHoofdmenu_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnHoofdmenu.Click
        Me.Visible = False
        My.Forms.FrmHoofdmenu.Visible = True

    End Sub

    'alternatief sql-data naar excel
    Private Sub Button2_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Button2.Click

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer
        'Dim r As Integer = 9 'rij 9 starten met invullen

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(System.AppDomain.CurrentDomain.BaseDirectory & "\tekeningenlijst1.xls")
        xlWorkSheet = xlWorkBook.Sheets("Blad1")

        'Invoegen van Werknummer en omschrijving
        xlWorkSheet.Range("B3").Value = My.Forms.FrmInlogscherm.WerkomschrijvingTextBox.Text
        xlWorkSheet.Range("B4").Value = My.Forms.FrmInlogscherm.PlaatsTextBox.Text
        xlWorkSheet.Range("B5").Value = My.Forms.FrmInlogscherm.CmbWerknummer.SelectedValue.ToString
        xlWorkSheet.Range("E4").Value = My.Forms.FrmInlogscherm.ArchitectTextBox.Text
        xlWorkSheet.Range("E5").Value = My.Forms.FrmInlogscherm.ConstructeurTextBox.Text

        'Invoegen van tekeningen in excel
        Dim z As Integer = 0
        Dim x As Integer = 0
        For i = 0 To TekeningenlijstDataGridView.RowCount - 1
            'ElseIf i = 1 Then '(1 < i) Andalso (i < 15) Then

            z = i + i
            '    For j = 0 To TekeningenlijstDataGridView.ColumnCount - 1
            '        If j = 0 Then
            '            xlWorkSheet.Cells(z + 10, j + 1) = _
            '                 TekeningenlijstDataGridView(j, i).Value.ToString() 'column, row
            '        ElseIf j = 1 Then
            '            xlWorkSheet.Cells(z + 11, 1) = _
            '                        TekeningenlijstDataGridView(j, i).Value.ToString() 'column, row
            '        ElseIf j > 1 Then
            '            xlWorkSheet.Cells(z + 11, j) = _
            '                        TekeningenlijstDataGridView(j, i).Value.ToString() 'column, row
            '        End If
            '    Next

            If i = 0 Then
                z = i + i
                For j = 0 To TekeningenlijstDataGridView.ColumnCount - 1
                    If j = 0 Then
                        xlWorkSheet.Cells(z + 10, j + 1) = _
                             TekeningenlijstDataGridView(j, i).Value.ToString() 'column, row
                        xlWorkSheet.Cells(z + 10, j + 1).Font.Bold = True
                    ElseIf j = 1 Then
                        xlWorkSheet.Cells(z + 11, 1) = _
                                    TekeningenlijstDataGridView(j, i).Value.ToString() 'column, row
                    ElseIf j > 1 Then
                        xlWorkSheet.Cells(z + 11, j) = _
                                    TekeningenlijstDataGridView(j, i).Value.ToString() 'column, row
                    End If
                Next
                x = (z + 11 + 1)

            ElseIf i > 0 AndAlso x < z AndAlso TekeningenlijstDataGridView(0, i).Value <> _
            TekeningenlijstDataGridView(0, (i - 1)).Value Then
                z = x
                For j = 0 To TekeningenlijstDataGridView.ColumnCount - 1
                    If j = 0 Then
                        xlWorkSheet.Cells(z + 10, j + 1) = _
                             TekeningenlijstDataGridView(j, i).Value.ToString() 'column, row
                        xlWorkSheet.Cells(z + 10, j + 1).Font.Bold = True
                    ElseIf j = 1 Then
                        xlWorkSheet.Cells(z + 11, 1) = _
                                    TekeningenlijstDataGridView(j, i).Value.ToString() 'column, row
                    ElseIf j > 1 Then
                        xlWorkSheet.Cells(z + 11, j) = _
                                    TekeningenlijstDataGridView(j, i).Value.ToString() 'column, row
                        'x = (z + 11 + 1) - 15 '15 kolommen, dus 15x for..next
                    End If
                Next
                x = (z + 11 + 1)

            ElseIf i > 0 AndAlso x > z AndAlso TekeningenlijstDataGridView(0, i).Value <> _
            TekeningenlijstDataGridView(0, (i - 1)).Value Then
                z = i + i
                If z > (x - 10) Then
                    z = (x - 10)
                End If
                For j = 0 To TekeningenlijstDataGridView.ColumnCount - 1
                    If j = 0 Then
                        xlWorkSheet.Cells(z + 10, j + 1) = _
                             TekeningenlijstDataGridView(j, i).Value.ToString() 'column, row
                        xlWorkSheet.Cells(z + 10, j + 1).Font.Bold = True
                    ElseIf j = 1 Then
                        xlWorkSheet.Cells(z + 11, 1) = _
                                    TekeningenlijstDataGridView(j, i).Value.ToString() 'column, row
                    ElseIf j > 1 Then
                        xlWorkSheet.Cells(z + 11, j) = _
                                    TekeningenlijstDataGridView(j, i).Value.ToString() 'column, row
                    End If
                Next
                x = (z + 11 + 1)

            ElseIf i > 0 AndAlso TekeningenlijstDataGridView(0, i).Value = _
            TekeningenlijstDataGridView(0, (i - 1)).Value AndAlso x < z Then
                'z = i + i
                For j = 0 To TekeningenlijstDataGridView.ColumnCount - 1
                    If j = 1 Then
                        xlWorkSheet.Cells(x, 1) = _
                                    TekeningenlijstDataGridView(j, i).Value.ToString() 'column, row
                    ElseIf j > 1 Then
                        xlWorkSheet.Cells(x, j) = _
                                    TekeningenlijstDataGridView(j, i).Value.ToString() 'column, row
                    End If
                    x = x + 1
                Next
            ElseIf i > 0 AndAlso x > z AndAlso TekeningenlijstDataGridView(0, i).Value = _
        TekeningenlijstDataGridView(0, (i - 1)).Value Then
                z = i + i
                If z > (x - 10) Then
                    z = (x - 10)
                End If
                For j = 0 To TekeningenlijstDataGridView.ColumnCount - 1
                    If j = 1 Then
                        xlWorkSheet.Cells(z + 10, 1) = _
                                    TekeningenlijstDataGridView(j, i).Value.ToString() 'column, row
                    ElseIf j > 1 Then
                        xlWorkSheet.Cells(z + 10, j) = _
                                    TekeningenlijstDataGridView(j, i).Value.ToString() 'column, row
                        'x = (z + 11 + 1) - 15 '15 kolommen, dus 15x for..next
                    End If
                Next
                x = (z + 10 + 1) 'KAN NIETIN FOR..NEXT LUS, TELT ER STEEDS WEER 15 VANAF ANDERS!
            End If
        Next


        ''Invoegen van tekeningen in excel
        'For i = 0 To TekeningenlijstDataGridView.RowCount - 1
        '    If i > 0 Then
        '        'Als bedrijfsnaam voor hetzelfde is als voorgaande dan bedrijfsnaam weglaten
        '        If TekeningenlijstDataGridView(0, i).Value = _
        '    TekeningenlijstDataGridView(0, (i - 1)).Value Then
        '            'als i>0 en bedrijfsnaam is hetzelfde dan 1 kolom naar rechts volgende rij
        '            For Z As Integer = 1 To TekeningenlijstDataGridView.ColumnCount - 1
        '                xlWorkSheet.Cells(i + 10, Z + 1) = _
        '            TekeningenlijstDataGridView(Z, i).Value.ToString() 'column, row
        '            Next
        '        Else
        '            'als i>0 maar bedrijfsnaam is niet hetzelfde dan geen kolom opschuiven
        '            For j = 0 To TekeningenlijstDataGridView.ColumnCount - 1
        '                xlWorkSheet.Cells(i + 10, j + 1) = _
        '            TekeningenlijstDataGridView(j, i).Value.ToString() 'column, row
        '            Next
        '        End If
        '    Else
        '        'als NIET i>0 dan geen kolom opschuiven
        '        For j = 0 To TekeningenlijstDataGridView.ColumnCount - 1
        '            xlWorkSheet.Cells(i + 10, j + 1) = _
        '        TekeningenlijstDataGridView(j, i).Value.ToString() 'column, row
        '        Next
        '    End If
        'Next


        'autosize used columns
        'xlWorkSheet.UsedRange.Columns.AutoFit()
        xlWorkSheet.Range("A9", "N500").Columns.AutoFit()

        'Printgebied instellen
        xlWorkSheet.PageSetup.PrintArea = "$A$1:$O$" & x

        'Pagina's instellen
        If xlWorkSheet.VPageBreaks.Count > 0 Then
            xlWorkSheet.VPageBreaks(1).Location = xlWorkSheet.Range("O1")
        End If

        'Printgebied opnieuw instellen omdat ik anders de vpagebreak niet op einde kon instellen
        xlWorkSheet.PageSetup.PrintArea = "$A$1:$N$" & x

        'Opslaan van Tekeningenlijst
        Dim datum As String = My.Computer.Clock.LocalTime.Date
        Dim tijd As String = My.Computer.Clock.LocalTime.Hour & " " & My.Computer.Clock.LocalTime.Minute
        'Dim strTekLijst As String = "C:\Documents and Settings\Hanzie\My Documents\Tekeningenlijst\" & "Tekeningenlijst" & " " & datum & " " & tijd & ".xls"
        Dim Directory As String = My.Forms.FrmInlogscherm.txtProjectdir.Text & "\Tekeningenlijst\"
        Dim Naam As String = "Tekeninglijst" & " " & My.Forms.FrmInlogscherm.CmbWerknummer.Text & " " & datum & " " & tijd & ".xls"

        If System.IO.Directory.Exists(Directory) Then
            xlWorkSheet.SaveAs(Directory & Naam)
        Else
            System.IO.Directory.CreateDirectory(Directory)
            xlWorkSheet.SaveAs(Directory & Naam)
        End If

        xlApp.Visible = True
        xlWorkSheet.Activate()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

    End Sub

    'Hoort bij alternatief sql-data naar excel
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

End Class